CREATE TABLE TMP_1 (a INT)
GO
INSERT INTO TMP_1 VALUES (1)
GO
CREATE TABLE TMP_2 (a INT, b INT)
GO

-- test INSERT SELECT with UNION or UNION ALL in procedure
CREATE PROCEDURE TEST_INSERT_SELECT
AS
INSERT INTO TMP_2 (a)
SELECT a FROM TMP_1
UNION
SELECT a FROM TMP_1
UNION
SELECT a FROM TMP_1

INSERT INTO TMP_2 (a)
SELECT a FROM TMP_1
UNION ALL
SELECT a FROM TMP_1
UNION ALL
SELECT a FROM TMP_1
GO

SELECT * FROM TMP_2
GO
EXECUTE TEST_INSERT_SELECT
GO
SELECT * FROM TMP_2
GO

-- test SELECT with UNION in procedure
CREATE PROCEDURE TEST_SELECT_1
AS
SELECT a FROM TMP_1
UNION
SELECT a FROM TMP_1
UNION
SELECT a FROM TMP_1
GO
EXECUTE TEST_SELECT_1
GO

-- test SELECT with UNION ALL in procedure
CREATE PROCEDURE TEST_SELECT_2
AS
SELECT a FROM TMP_1
UNION ALL
SELECT a FROM TMP_1
UNION ALL
SELECT a FROM TMP_1
GO
EXECUTE TEST_SELECT_2
GO

DROP TABLE TMP_1
GO
DROP TABLE TMP_2
GO
DROP PROCEDURE TEST_INSERT_SELECT
GO
DROP PROCEDURE TEST_SELECT_1
GO
DROP PROCEDURE TEST_SELECT_2
GO
